<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
header('Content-type: text/html; charset=utf-8');
$link = mysql_connect("localhost", "root", "laropavieja.net.co") or die(mysql_error());
//$link = mysql_connect("localhost", "root", "ornitorrinco666") or die(mysql_error());
mysql_select_db("corpoica", $link) or die(mysql_error());
mysql_query("SET NAMES 'utf8'");
$str = "<table border=1 style=\"font-size:small \"><tr><td></td><td>DEPARTAMENTO</td><td>MUNICIPIO</td><td>INSCRITO</td><td>TIPO</td><td>ESTADO</td></tr>";
$cont=0;
if ($fp = fopen("municipios.csv", "r")) {
    while (( $data = fgetcsv($fp, 20000, ",", "\"")) !== false) {
        $departamento = $data[2];
        $municipio = strtoupper($data[1]);
        $sql = "SELECT * FROM departaments WHERE nombre ='$departamento'";
        $encontrado_dep = 0;
        $encontrado_mun = 0;
        $query = mysql_query($sql, $link) or die(mysql_error());

        while ($row = mysql_fetch_array($query)) {
            $encontrado_dep = 1;
            $sql1 = "SELECT * FROM cities WHERE  departament_id=" . $row['id'] . " AND nombre ='$municipio'";
            $res = mysql_query($sql1, $link) or die(mysql_error());

            $divipola = "";
            while ($row1 = mysql_fetch_array($res)) {


                $encontrado_mun = 1;
                $sql2 = "SELECT * FROM user_cities WHERE city_id =" . $row1['id'];
                $res2 = mysql_query($sql2, $link) or die(mysql_error());
                $inscrito = "NO";
                while ($row2 = mysql_fetch_array($res2)) {
                    $inscrito = "SI";
                    $sql3 = "SELECT * FROM users WHERE id=" . $row2['user_id'];
                    $res3 = mysql_query($sql3, $link) or die(mysql_error());
                    while ($row3 = mysql_fetch_array($res3)) {
                        
                        if($row3['branch_id']==1){
                            $tipo="Municipio";
                        }elseif($row3['branch_id']==2){
                            $tipo="CPGA";
                        }
                        
                        if($row3['finalizado']==1){
                            $estado="FINALIZADO";
                        }elseif($row3['finalizado']==0){
                            $estado="SIN FINALIZAR";
                        }
                        
                       $cont++;
                        $str.="<tr><td>$cont</td><td>" . $departamento . "</td><td>" . $municipio . "</td><td>" . $inscrito . "</td><td>" . $tipo. "</td><td>" . $estado. "</td></tr>";
                    }
                }
                if ($inscrito == "NO") {
                    $cont++;
                    $str.="<tr><td>$cont</td><td>" . $departamento . "</td><td>" . $municipio . "</td><td>" . $inscrito . "</td><td>NO</td><td>NO</td></tr>";
                }
            }
            if ($encontrado_mun == 0) {
                echo $departamento . " " . $municipio . " <br>";
            }
        }

        if ($encontrado_dep == 0) {
            echo $departamento . "<br>";
        }
    }
} else {
    echo"ERROR";
}

$str.="</table>";
echo $str;
?>
